﻿Imports Microsoft.VisualBasic
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.IO

Public Class ExcelImportCheck

    '檢查一：讀取資料 
    Public Function LoadData(ByRef dt As Data.DataTable, ByVal FilePath As String, ByVal SheetName As String) As Boolean
        'read excel file 
        Dim strConn As String = ""
        If FilePath.ToUpper().IndexOf(".XLSX") = -1 Then
            strConn = ("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=") + FilePath & ";" & "Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'" 'IMEX=1,強制以字串型式輸出
        Else
            strConn = ("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=") + FilePath & ";" & "Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'"
        End If

        Dim Conn As New OleDbConnection(strConn)
        Conn.Open()
        Dim dt1 As Data.DataTable = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
        Dim con As Boolean = False
        For i As Integer = 0 To dt1.Rows.Count - 1
            If dt1.Rows(i)("TABLE_NAME").ToString().Trim() = SheetName & "$" Then
                con = True
                i = dt1.Rows.Count - 1
            End If
        Next
        Conn.Dispose()

        If Not con Then
            MyObj.ShowMessage("匯入失敗!!請檢查工作表名稱是否為[" & SheetName & "]!!")
            dt.Dispose()
            File.Delete(FilePath)
            Return False
        Else
            Dim myCommand As New OleDbDataAdapter("SELECT * FROM [" & SheetName & "$]", strConn)
            Dim myDataSet As New Data.DataSet()
            myCommand.Fill(myDataSet, "ExcelInfo")
            dt = myDataSet.Tables("ExcelInfo")
            File.Delete(FilePath)
            Return True
        End If
    End Function

    '檢查二：dt欄位 
    Public Function CheckColumn(ByRef dt As Data.DataTable, ByRef ColumnList As String(,)) As Boolean
        Dim i, j As Integer
        For i = 0 To dt.Columns.Count - 1
            For j = 0 To (ColumnList.Length / 2) - 1
                If ColumnList(j, 1) <> "Y" Then
                    If dt.Columns(i).ColumnName.Trim() = ColumnList(j, 0) Then
                        ColumnList(j, 1) = "Y"
                        j = ColumnList.Length - 1
                    End If
                End If
            Next
        Next

        Dim AllColumnName As String = ""
        For j = 0 To (ColumnList.Length / 2) - 1
            If ColumnList(j, 1) = "N" Then
                If AllColumnName = "" Then
                    AllColumnName &= "[" & ColumnList(j, 0) & "]"
                Else
                    AllColumnName &= "、[" & ColumnList(j, 0) & "]"
                End If
            End If
        Next

        If AllColumnName <> "" Then
            MyObj.ShowMessage("匯入失敗!!請檢查欄位名稱是否包括以下欄位：\n\n" & AllColumnName & "!!")
            Return False
        Else
            Return True
        End If

    End Function
End Class
